The present invention generally relates to an Extraction, Transformation and Loading (ETL) designer module of a computerized financial system that operates to communicate instructions to ETL services module of a server to configure ETL packages. The ETL packages are used to extract, transform and load elements from a source financial table contained in a source database or data store into a destination table of a destination database or data store having a pre-defined format that is independent of the format of the first table.
Computerized financial systems include various software packages including accounting programs and financial reporting programs. The accounting programs maintain various accounts for the business such as a general ledger, inventory, accounts receivable, accounts payable, balances, payroll and other accounts. The general ledger is a storehouse for financial activity and history of a company, which contains all of the financial transactions for the company. The general ledger and other accounts are typically stored as tables (financial tables) in one or more databases.
Financial reporting applications utilize the data stored in the financial tables of the databases to generate reports. Such reports can include balance reports and forecasting reports, for example.
The databases that contain the financial tables of a business are conventionally organized and maintained using a variety of Database Management Systems (DBMS). Among such database systems, are those adhering to a “relational” model which are recognized as Relational Database Management Systems (RDBMS). A relational database is a collection of data that is organized in related two-dimensional tables of columns and rows. Data in a table can be accessed and manipulated by performing set operations on the tables, such as join, sort, merge, and so on. These operations are typically initiated by way of a user-defined query that is constructed in a query language such as Structured Query Language (SQL). SQL queries consist of high level commands which typically describe the data set to be selected, retrieved or processed.
As mentioned above, the financial reporting programs need to access the financial information of the business in one or more financial tables to extract information that will be utilized to form the report. Some prior art financial reporting programs or tools include program code (e.g., SQL statements) in their software to directly access particular financial tables of a database. One problem with such an approach is that, when a financial table is modified or added, the program code of the reporting program needs to be modified to provide access to the modified or new financial table. Such modifications are complex, time consuming and expensive to complete.
An alternative approach to the above is to make use of existing Extraction Transformation and Loading (ETL) services provided with some servers. ETL services provide a set of tools for extracting, transforming and consolidating data from disparate sources into single or multiple destinations to build data warehouses or data marts in accordance with a predefined format. Data Transformation Services (DTS) of Microsoft® SQL Server is one example of an ETL service.
ETL services allow for the creation of ETL packages (DTS Packages for Microsoft® SQL Server) that define custom data movement solutions that are tailored to the financial reporting application. Each ETL package generally defines multiple steps of associated tasks that operate to perform the desired extraction and transformation of the data elements contained in the source table. Additionally, execution of the ETL package causes the ETL to import the specified source data of the source table, transform the data into the desired format, and export the data to the destination table in the destination database on store. As a result, such ETL services can be used to form an ETL package that moves selected data elements from a source financial table (e.g., a general ledger), transform the data elements into a desired format, and load the data elements into a reporter table that is ready for use by the financial reporting application. Additionally, modifications can be made directly to the ETL packages to accommodate desired modifications without having to modify the program code of the financial reporting application.
Although the use of such ETL services avoids the need to customize the reporter application code to a particular financial table, the ETL packages that define the custom data movement must still be programmed and configured. ETL services include a user interface (such as Enterprise Manager for DTS of Microsoft® SQL Server) that allows the user to directly configure the DTS Packages. Such programming is time-consuming and must be performed by someone that not only has a complete understanding of the destination table that is desired by the financial reporting application including the desired source data and the desired format of the data, but also a knowledge of how to program or configure the ETL packages to perform the extraction, transformation and loading steps. Additionally, the configuring of the ETL packages can be complex due to the multiple steps and associated tasks that must be manually entered to perform the desired data extraction, transformation, or loading operation.
A need exists for improved methods of programming or configuring ETL packages for use by financial reporting applications including making such configuring methods more efficient and user friendly.